ajJSONFromTable function

Available since AlchemyJ v4.0

Description

The ajJSONFromTable function returns a JSON string of a single table.

In some cases, we want to create a JSON string that contains a name with a value which is a JSON array as shown below.

{
    "object":[

        {"array1":"value1"},

        {"array2":"value2"},

        {"array3":"value3"}

    ]
}

Syntax

ajJSONFromTable(header, data, [data_name], [data_type], [exclude_column])

Argument Name	Argument Type	Description
header (required)	Range / Array	The range of cells to be set as the name of the JSON name-value pair. It can be selected either vertically or horizontally.
data (required)	Range / Array	The range of cells to be set as the value of the JSON name-value pair. It can be selected either vertically or horizontally but the orientation must be the same as ‘Header’.
data_name (optional)	String	The name of the JSON name-value pair. The default value is "data".
data_type (optional)	String	The data type of the fields (‘Header’) in the format below. For the list of data types, please refer to the data type supported in AlchemyJ. The fields that do not exist will be ignored. <field_name>-##-<data_type>-||-<field_name>-##-<data_type>-||-... Where, -||- denotes AlchemyJ parameter separator. -##- denotes AlchemyJ parameter type separator. For example, ID-##-number-||-Name-##-string Please take note that both separator symbols can be configured in %%AppConfig worksheet. The default data type will follow the cell data type.
exclude_column (optional)	String	The fields to be excluded from the result. The fields that do not exist will be ignored. <field_name>-||-<field_name>-||-... Where, -||- denotes AlchemyJ parameter separator. For example, ID-||-Last Name Please take note that the parameter separator symbols can be configured in %%AppConfig worksheet. No fields will be excluded by default.
special_value_as_empty (optional)	Boolean	If it equals TRUE, to enable treating specified values as empty string (applies to string fields only). If it equals FALSE, same as before logic, keep the empty string. The default value is FALSE.
special_value (optional)	String	It is invalid when special_value_as_empty is TRUE, it will replace the empty string to the special_value to the table.

The function will return:

1) Return Value: JSON string

2) Return Type: Single Value / Multiple values (array formula)

Example

Here are some examples of using the ajJSONFromTable function.

Example 1

There is one table, the table data can come from file (can use ajReadFile or ajReadWorkbook to read data from file), database tables (can use ajDBReadRecord to read record from database) and also can add static data in the workbook.

In below case generate a json only with header and data parameters.

Returned JSON:

{
    "data": [
        {
            "Product ID": "P001",
            "Product Name": "Xbox One",
            "Price": 1000,
            "On Sale": true
        },
        {
            "Product ID": "P002",
            "Product Name": "PS4",
            "Price": 999.9,
            "On Sale": false
        },
        {
            "Product ID": "P003",
            "Product Name": "Nintendo Switch",
            "Price": 1500,
            "On Sale": true
        }
    ]
}

Example 2

For below case, customize the data_name as productData, and set the On Sale data type is string and Price data type is integer. exclude_column is Product Name, so after generate json the node name is productData, the On Sale data type is string, the Price data type is integer, and the Product Name will exclude in the json.

Returned JSON:

{
    "productData": [
        {
            "Product ID": "P001",
            "Price": 1000,
            "On Sale": "true"
        },
        {
            "Product ID": "P002",
            "Price": 1000,
            "On Sale": "false"
        },
        {
            "Product ID": "P003",
            "Price": 1500,
            "On Sale": "true"
        }
    ]
}

Example 3

For below case, it has some empty string data, after generation the JSON string, still want to keep these empty fields in the JSON string. Can use special_value_as_empty and special_value parameters to do it.

The Product Name is empty for Product ID is P002, Want to show empty string for Product Name.

Set a special value PS4 into the empty field in the table, then use special_value_as_empty and special_value to keep the empty string in the JSON string.

Returned JSON:

{
    "data": [
        {
            "Product ID": "P001",
            "Product Name": "Xbox One",
            "Price": 1000,
            "On Sale": true
        },
        {
            "Product ID": "P002",
            "Product Name": "",
            "Price": 999.9,
            "On Sale": false
        },
        {
            "Product ID": "P003",
            "Product Name": "Nintendo Switch",
            "Price": 1500,
            "On Sale": true
        }
    ]
}

Note:

1. If the JSON string is too large, the return value can be specified in a row cell array.

   2. If you want to convert the JSON back to the table, you can refer to ajJSONToTable do the conversion.
   3. If you want to generate a JSON string base on multiple tables, can use ajJSONKeyGeneration with ajJSONFromSchema to do it.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Failed to convert data to specified data type.
Invalid data type, it could be number, integer, boolean or string.
Header or data range does not exist.